package com.jiazhong.basic.pack0524.onetomany.dao;


import com.jiazhong.basic.pack0524.db.DBManager;
import com.jiazhong.basic.pack0524.onetomany.bean.Emp;
import lombok.SneakyThrows;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class EmpDAO {

    private Connection conn;
    private PreparedStatement ps;
    private ResultSet rs;
    @SneakyThrows
    public List<Emp> findByDeptNo(int deptNo){
        List<Emp> list = new ArrayList<>();
        conn = DBManager.getConnection();
        String sql = "select * from emp where state=1 and deptno=?";
        ps = conn.prepareStatement(sql);
        ps.setInt(1,deptNo);
        rs = ps.executeQuery();
        while(rs.next()){
            Emp emp = new Emp();
            emp.setEmpNo(rs.getInt(1));
            emp.setEname(rs.getString(2));
            emp.setJob(rs.getString("job"));
            emp.setMgr(rs.getInt(4));
            emp.setHireDate(rs.getString(5));
            emp.setSal(rs.getDouble(6));
            emp.setComm(rs.getDouble("comm"));
            emp.setDeptNo(rs.getInt(8));
            emp.setState(rs.getInt(9));
            list.add(emp);
        }
        return list;
    }
}
